<?php
namespace app\dao;
/**
 * @desc 分仓库存表
 * @author liaojianwen
 * @date 2016-12-26
 * @return StockPileDAO
 */
use Yii;
use app\dao\BaseDAO;
use app\enum\EnumOther;
use yii\base\Object;
use yii\db\Query;
use app\dao\ProductUnitDAO;
class StockPileDAO extends BaseDAO
{
	/**
	 * @desc 构造函数
	 * @author liaojianwen
	 * @date 2016-12-14
	 */
	public function __construct()
	{
		parent::__construct();
		$this->_table = 'stock_pile';
		$this->_pKey ='stock_pile_id';
	}
	
	/**
	 * @inheritdoc
	 */
	public static function tableName()
	{
		return '{{%stock_pile}}';
	}
	
	/**
	 * @desc 获取分仓表唯一的商品列表
	 * @author liaojianwen
	 * @date 2016-12-28
	 */
	public function findByProduct($cond, $pageInfo)
	{
		$limit = $pageInfo ['pageSize'];
		$offset = ($pageInfo ['page'] - 1) * $limit;
		$query = new Query();
		$select = "s.product_id,product_name,unit_name,p.price";
	    $query->select($select)
				->from("$this->_table s")
				->leftJoin("product p","p.product_id = s.product_id")
				->leftJoin("unit u","u.unit_id = s.quantity_unit")
				->groupBy(['s.product_id','unit_name']);
	    
	    if(isset($cond['pname']) && $cond['pname']){
	    	$query->andwhere(['like',"product_name",$cond['pname']]);
	    }
	    $result ['count'] = $query->count ();
		$result ['list'] = $query->offset ( $offset )->limit ( $limit )->all ();
		$result ['page'] = array (
				'page' => $pageInfo ['page'],
				'pageSize' => $pageInfo ['pageSize']
		);
		return $result;
	}
	
	/**
	 * @desc 分仓表中各个仓库的数量
	 * @param $pid [] product_id
	 * @author liaojianwen
	 * @date 2016-12-28
	 */
	public function quantity($pid)
	{
// 		$query = new Query();
// 		$selects = "s.quantity,warehouse_name";
// 		$selects = "IFNULL(quantity,0)quantity, w.warehouse_id,warehouse_name,product_id";
// 		$result = $query->select($selects)->from("$this->_table s")
// 		->rightJoin("warehouse w","w.warehouse_id = s.warehouse_id and product_id =".$pid)
// 		->where('product_id =:pid',[':pid'=>$pid])
// 		->all();
		$sql = "select IFNULL(quantity,0) quantity, w.warehouse_id,warehouse_name,product_id"
				." from stock_pile s"
				." RIGHT JOIN warehouse w on w.warehouse_id = s.warehouse_id and s.product_id =".$pid
				." where w.delete_flag =".EnumOther::NO_DELETE
				." order by w.warehouse_id ASC";
		$Command = Yii::$app->db->createCommand();
		$result= $Command->setSql($sql)->queryAll();
		return $result;
	}
	
	/**
	 * @desc 根据仓库id 获取商品信息
	 * @author liaojianwen
	 * @date 2017-01-21
	 */
	public function getProductByWare($ware_id, $name, $pageInfo)
	{
		$limit = $pageInfo ['pageSize'];
		$offset = ($pageInfo ['page'] - 1) * $limit;
		$selections = "s.product_id,p.product_name,p.product_sn,s.quantity,unit_name,s.quantity_unit,price,unit_content,p.supply_price,p.guide_price";
		$conditions = "s.warehouse_id = :ware";
		$params = array (
				':ware' => $ware_id,
		);
		
		$query = new Query ();
		$query->select ( $selections )
		->from ("$this->_table s")
		->leftJoin("product p","p.product_id = s.product_id")
		->where ( $conditions, $params );
		//查询条件
		if (! empty ( $name )) {
			$query->andWhere (['like', 'CONCAT(product_name,p.product_id)', $name]);//andFilterCompare
		}
		$query->leftJoin("unit u","s.quantity_unit = u.unit_id")
		->orderBy ( ['s.create_time'=>SORT_ASC]);
		
		//查询条件
		$result ['count'] = $query->count ();
		$result ['list'] = $query->offset ( $offset )->limit ( $limit )->all ();
		foreach ($result['list'] as &$list){
			$res_unit = ProductUnitDAO::getInstance()->getUnits($list['product_id']);
			$list['product_unit'] = $res_unit;
		}
		$result ['page'] = array (
				'page' => $pageInfo ['page'],
				'pageSize' => $pageInfo ['pageSize']
		);
		return $result;
	}
	
	/**
	 * @desc 调用InsertStockPile 新增
	 * @param int $product_id
	 * @param int $warehouse_id 默认仓库id
	 * @author liaojianwen
	 * @date 2017-02-09
	 */
	public function InsertStockPile($product_id,$warehouse_id)
	{
		$sql = "call InsertStockPile({$product_id}, {$warehouse_id})";
		$command = Yii::$app->db;
		$res = $command->createCommand()->setSql($sql)->execute();
	}
	
	/**
	 * @desc 检查是否有库存出库
	 * @param $wid 仓库id
	 * @param $product_id 商品id
	 * @author liaojianwen
	 * @date 2017-02-23
	 */
	public function checkQty($wid,$product_id)
	{
		$query = new Query();	
		$selects = 's.quantity,s.quantity_unit,product_name';
		$conditions = "s.warehouse_id = :wid and s.product_id =:pid";
		$params = [ 
				':wid' => $wid,
				':pid' => $product_id
		];
		$result = $query->select($selects)
			->from("$this->_table s")
			->leftJoin("product p","p.product_id = s.product_id")
			->where($conditions, $params)
			->one();
		return $result;
			
	}
	
	
	/**
	 * @desc 根据拆装单仓库id 获取商品信息
	 * @author liaojianwen
	 * @date 2017-11-21
	 */
	public function getDisassemblyProductByWare($ware_id, $name, $pageInfo)
	{
		$limit = $pageInfo ['pageSize'];
		$offset = ($pageInfo ['page'] - 1) * $limit;
		$selections = "p.product_id,p.product_name,p.product_sn,ifnull(s.quantity,0) quantity,ifnull(u.unit_name,v.unit_name) unit_name,ifnull(s.quantity_unit,p.quantity_unit) quantity_unit,price,unit_content,p.supply_price,p.guide_price";
		
// 		$sql = "select p.product_id,p.product_name,p.product_sn,ifnull(s.quantity,0) quantity,ifnull(unit_name,'') unit_name,ifnull(s.quantity_unit,0) quantity_unit,price,unit_content,p.supply_price,p.guide_price
		$sql = "select ";
		$sql_where=" from {$this->_table} s right join product p on p.product_id = s.product_id and s.warehouse_id = {$ware_id}
				left join unit u on s.quantity_unit = u.unit_id left join unit v on p.quantity_unit = v.unit_id";
		if(! empty($name)){
			$sql_where .=" where  concat(product_name,p.product_id) like '%{$name}%' and p.delete_flag = 0";
		}
		$sql_where .= " order by p.product_id ASC";
		
		$command = Yii::$app->db;
		
		$sql_count = $sql."count(p.product_id) ".$sql_where;
		
		$result ['count'] = $command->createCommand()->setSql($sql_count)->queryScalar();
		
		$sql_query = $sql . $selections .$sql_where ." limit {$limit} offset {$offset}";
		$result ['list']  = $command->createCommand()->setSql($sql_query)->queryAll();
	
		//查询条件
		foreach ($result['list'] as &$list){
			$res_unit = ProductUnitDAO::getInstance()->getUnits($list['product_id']);
			$list['product_unit'] = $res_unit;
		}
		$result ['page'] = array (
				'page' => $pageInfo ['page'],
				'pageSize' => $pageInfo ['pageSize']
		);
		return $result;
	}
	
	/**
	 * @desc 核算各个仓库数量(不包含报废仓)
	 * @author liaojianwen
	 * @date 2018-04-17
	 */
	public function getAccountStock($goods_id)
	{
		$selections = "product_id,sum(quantity) quantity";
		$condition = "product_id =:gid";
		$params = [
				':gid' => $goods_id
		];
	
		$query = new Query();
		$result = $query->select($selections)
		->from("$this->_table t")
		->innerJoin("warehouse w","w.warehouse_id = t.warehouse_id and w.is_scrapped !=".EnumOther::SCRAPPED)
		->where($condition,$params)
		->groupBy('product_id')
		->one();
		return $result;
	}
	
}
